﻿Imports Microsoft.VisualBasic
Imports System.Security.Cryptography
Imports System.ComponentModel.Component
Imports System.Globalization
Imports System
Imports System.IO
Imports System.DirectoryServices
Imports ZedGraph


Module SQLCommand

    Function SQL_Insert(ByVal aArray As Object, ByVal tName As String) As Boolean

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim adapterTable As SqlClient.SqlDataAdapter = Nothing

        Dim s As String = ""
        Dim i As Integer = 0


        SQL_Insert = True

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        s = "('"
        While i < aArray.length()
            s = s & aArray(i).ToString.Replace("'", "''") & "','"
            i = i + 1
        End While
        Dim dte As DateTime = Now

        s = s.Substring(0, s.Length - 2) & ",'" & dte.ToString("yyyy-MM-dd HH:mm:ss") & "')"

        cmd.CommandText = "INSERT INTO " & tName & " (" & My.Settings.Item(tName) & ") VALUES " & s

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            SendEMail("INSERT INTO " & tName & " (" & My.Settings.Item(tName) & ") VALUES " & s & "   *******   " & ex.ToString, "jean-luc@record.ch", "Error import data to SZH081")
            SQL_Insert = False
        End Try

        sqlConnection1.Close()

    End Function

    Public Sub SendEMail(ByVal Message As String, ByVal EMailAddress As String, ByVal Subject As String)
        Dim EMail As New System.Net.Mail.MailMessage
        Dim SMTPServer As New System.Net.Mail.SmtpClient
        'Dim Authentication As New Net.NetworkCredential("login name/email", "password")

        EMail.To.Add(EMailAddress)
        EMail.From = New System.Net.Mail.MailAddress(My.Settings.EmailFrom)
        EMail.Body = Message
        EMail.Subject = Subject

        SMTPServer.Host = My.Settings.EmailServer
        SMTPServer.Port = 25
        'SMTPServer.Credentials = Authentication

        SMTPServer.Send(EMail)
    End Sub

    Function GetSetupValue(ByVal pName As String) As String

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim s As String = ""

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = "SELECT pValue FROM F0002 WHERE PName = '" & pName & "'"

        reader = cmd.ExecuteReader()

        If reader.Read Then
            s = reader(0).ToString
        Else
            s = "1"
        End If

        sqlConnection1.Close()

        If s = "1" Then
            sqlConnection1.Open()

            cmd.CommandText = "INSERT INTO F0002 (PName, PValue) VALUES ('" & pName & "','Set a value')"
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
            End Try
            sqlConnection1.Close()
        End If


        GetSetupValue = s
    End Function

    Function GetSerialValue(ByVal ID As String) As String

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim s As String = ""
        Dim q As String = ControlChars.Quote

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = "select Seriennummer from TUER where ID = '" & ID & "'"

        GetSerialValue = ""

        reader = cmd.ExecuteReader()

        If reader.Read Then
            GetSerialValue = reader(0).ToString
        End If

        sqlConnection1.Close()

    End Function

    Function SetSerialValue(ByVal ID As String, ByVal Serial As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim s As String = ""
        Dim q As String = ControlChars.Quote

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = "update TUER set Seriennummer = '" & Serial & "' where ID =" & ID

        AddMessage("update TUER set Seriennummer = '" & Serial & "' where ID =" & ID)

        SetSerialValue = True

        cmd.ExecuteNonQuery()
        sqlConnection1.Close()

    End Function

    Function GetItem_Location(ByVal ID As String, ByRef Item As String, ByRef Location As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing


        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = "select artikelnummer, lagerort from eteil where id = '" & ID & "'"

        reader = cmd.ExecuteReader()


        If reader.Read Then
            Item = reader(0).ToString
            Location = reader(1).ToString
        End If

        sqlConnection1.Close()
        GetItem_Location = True
    End Function

    Function SetItem_Location(ByVal ID As String, ByRef Item As String, ByRef Location As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = "update eteil set artikelnummer = '" & Item & "', lagerort = '" & Location & "'   where id = '" & ID & "'"
        cmd.ExecuteNonQuery()
        AddMessage("update eteil set artikelnummer = '" & Item & "', lagerort = '" & Location & "'   where id = '" & ID & "'")

        sqlConnection1.Close()
        SetItem_Location = True
    End Function

    Function ConfirmError(ByVal ID As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim s As String = ""
        Dim q As String = ControlChars.Quote

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = "update F0003 set CONF = 'Y' where ID = " & ID

        ConfirmError = True

        cmd.ExecuteNonQuery()
        sqlConnection1.Close()

    End Function

    Function ExportAUF(ByVal AUFRef As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        'Connection to Cadis DB Server
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim item As String = ""
        Dim Price As String = ""
        Dim TPrice As String = ""
        Dim s As String = ""
        Dim i As Integer = 0
        Dim AUF_ID As String = ""
        Dim TUER_ID As String = ""
        Dim KUNDE_ID As String = ""
        Dim Inventory As Boolean
        Dim AufDate As String = ""
        Dim AufArt As String = ""
        Dim AufDesc As String = ""
        Dim SerialDoor As String = ""
        Dim tDate As Date
        Dim Status As String = ""
        Dim AufNummer As String = ""
        Dim Einbruch As Boolean = False

        'Defined output file
        Dim appendMode As Boolean = False
        Dim sw As New StreamWriter(My.Settings.sPathtoM3 & "\" & AUFRef & ".txt", appendMode)

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        'Get AUF line
        cmd.CommandText = "SELECT * FROM AUF WHERE ID = '" & AUFRef & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            AUF_ID = reader(0).ToString
            For i = 2 To 18
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(19).ToString
            sw.WriteLine(s)
            If reader(10).ToString = "91" Then
                Inventory = True
            Else
                Inventory = False
            End If
        End If

        'Get order date for small material
        tDate = reader(20).ToString
        AufDate = tDate.ToString("yyyyMMdd")
        AufArt = reader(7).ToString
        Status = Trim(reader(15).ToString)
        AufNummer = Trim(reader(6).ToString)

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get KUNDE line
        cmd.CommandText = "SELECT * FROM KUNDE WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            KUNDE_ID = reader(0).ToString
            s = ""
            For i = 2 To 10
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(11).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get OBJEKT line
        cmd.CommandText = "SELECT * FROM OBJEKT WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            For i = 2 To 20
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(21).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get TUER line
        cmd.CommandText = "SELECT * FROM TUER WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            TUER_ID = reader(0).ToString
            s = ""
            For i = 2 To 12
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(13).ToString
            sw.WriteLine(s)
        End If

        'Get serial number for small material
        SerialDoor = reader(4).ToString
        AufDesc = reader(3).ToString
        Dim tmp1 As String = ""
        Dim tmp2 As String = ""
        Dim tmp3 As String = ""

        sqlConnection1.Close()
        sqlConnection1.Open()

        'check if small material is needed

        'Check if Einbruch
        If Not Inventory Then
            cmd.CommandText = "SELECT Fehlercode FROM ETEIL WHERE IDm = '" & TUER_ID & "'"
            reader = cmd.ExecuteReader()
            While reader.Read
                s = Trim(reader(0).ToString)
                If s = "30" Or s = "40" Or s = "50" Or s = "60" Or s = "70" Or s = "80" Then
                    Einbruch = True
                End If
            End While
        End If

        sqlConnection1.Close()

        'Add small material method CH
        If My.Settings.Check_Small_Mat = "CH" Then
            sqlConnection1.Open()

            Dim a As Boolean
            Dim b As Boolean

            CheckSM(SerialDoor, AufDate, AufArt, AufDesc, tmp1, tmp2, tmp3, a, b)
            If Not Inventory And Not Status = "X" Then
                cmd.CommandText = "SELECT ID FROM ETEIL WHERE IDm = '" & TUER_ID & "'"

                reader = cmd.ExecuteReader()
                If reader.Read Then
                    'With material and not closed per phone
                    If a And Status <> "T" Or Einbruch Then
                        AddSM(TUER_ID, GetTotalPrice(TUER_ID, AufNummer), "930")
                    End If

                Else
                    'without material and not closed per phone
                    If b And Status <> "T" Or Einbruch Then
                        AddSM(TUER_ID, GetTotalPrice(TUER_ID, AufNummer), "930")
                    End If

                End If
            End If

            sqlConnection1.Close()
        End If

        sqlConnection1.Open()

        'Get WART_LIST line
        cmd.CommandText = "SELECT * FROM WART_LIST WHERE IDm = '" & TUER_ID & "'"

        reader = cmd.ExecuteReader()
        While reader.Read
            s = ""
            For i = 2 To 3
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(4).ToString
            sw.WriteLine(s)
        End While

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get TAET line
        cmd.CommandText = "SELECT * FROM TAET WHERE IDm = '" & TUER_ID & "'"

        reader = cmd.ExecuteReader()
        While reader.Read
            s = ""
            For i = 2 To 5
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(6).ToString
            sw.WriteLine(s)
        End While

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get ETEIL line
        If Not Inventory Then
            cmd.CommandText = "SELECT * FROM ETEIL WHERE IDm = '" & TUER_ID & "'"

            reader = cmd.ExecuteReader()
            While reader.Read
                s = ""
                For i = 2 To 10
                    s = s & reader(i).ToString & ";"
                Next
                s = s & reader(11).ToString
                sw.WriteLine(s)
            End While
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'End TUER
        s = "/TUER"
        sw.WriteLine(s)

        'Get REPORT line
        cmd.CommandText = "SELECT * FROM REPORT WHERE IDm = '" & KUNDE_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then

            s = ""
            For i = 2 To 19
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(20).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()



        'Get SPESEN line
        cmd.CommandText = "SELECT * FROM SPESEN WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            For i = 2 To 6
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(7).ToString
            sw.WriteLine(s)
        End If

        'End AUF
        s = "/AUF"
        sw.WriteLine(s)

        sw.Close()
        sqlConnection1.Close()
        ExportAUF = True

    End Function

    Function DispatchAUF(ByVal AUFRef As String, ByVal Tech As String, ByVal Region As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        'Connection to Cadis DB Server
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing

        Dim sqlConnection2 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd2 As New System.Data.SqlClient.SqlCommand
        Dim reader2 As SqlClient.SqlDataReader = Nothing

        Dim item As String = ""
        Dim Price As String = ""
        Dim TPrice As String = ""
        Dim s As String = ""
        Dim i As Integer = 0
        Dim AUF_ID As String = ""
        Dim TUER_ID As String = ""
        Dim KUNDE_ID As String = ""
        Dim Inventory As Boolean
        Dim AufDate As String = ""
        Dim AufArt As String = ""
        Dim AufDesc As String = ""
        Dim SerialDoor As String = ""
        Dim Status As String = ""
        Dim AufNummer As String = ""
        Dim Einbruch As Boolean = False
        Dim s1 As String = ""

        'Defined output file
        Dim appendMode As Boolean = False
        s = My.Settings.sPathToDispatch & "\" & Trim(AUFRef) & ".txt"
        Dim sw As New StreamWriter(My.Settings.sPathToDispatch & "\" & Trim(AUFRef) & ".txt", appendMode, System.Text.Encoding.Default)

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1

        cmd2.CommandType = System.Data.CommandType.Text
        cmd2.Connection = sqlConnection2

        sqlConnection1.Open()

        'Get AUF line
        cmd.CommandText = "SELECT * FROM AUF WHERE Auftragsnummer = '" & AUFRef & "' AND Direction = 'S'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            AUF_ID = reader(0).ToString
            For i = 2 To 18

                s1 = reader(i).ToString

                If i = 14 Then
                    s1 = Format(Date.Today, "yyyyMMdd") & Format(TimeOfDay, "HHmm")
                End If

                If i = 15 Then
                    s1 = "D"
                End If

                If i = 16 Then  'Change start date
                    s1 = Format(Dispatch.DateEdit1.DateTime, "yyyyMMdd") & Format(Dispatch.TimeEdit1.Time, "HHmm")
                End If

                If i = 17 Then  'Change stop date
                    s1 = Format(Dispatch.DateEdit1.DateTime, "yyyyMMdd") & Format(Dispatch.TimeEdit2.Time, "HHmm")
                End If

                s = s & s1 & ";"
            Next

            s = s & reader(19).ToString
            sw.WriteLine(s)
            If reader(10).ToString = "91" Then
                Inventory = True
            Else
                Inventory = False
            End If
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get KUNDE line
        cmd.CommandText = "SELECT * FROM KUNDE WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            KUNDE_ID = reader(0).ToString
            s = ""
            For i = 2 To 10
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(11).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get OBJEKT line and change the region and technicien
        cmd.CommandText = "SELECT * FROM OBJEKT WHERE IDm = '" & AUF_ID & "'"
        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            s1 = ""
            For i = 2 To 20
                s1 = reader(i).ToString

                If i = 17 Then
                    s1 = Trim(Tech)
                End If

                If i = 19 Then
                    s1 = Trim(Region)
                End If

                s = s & s1 & ";"
            Next

            s = s & reader(21).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get TUER line
        cmd.CommandText = "SELECT * FROM TUER WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        While reader.Read
            TUER_ID = reader(0).ToString
            s = ""
            For i = 2 To 12
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(13).ToString
            sw.WriteLine(s)

            'Get WART_LIST line
            cmd2.CommandText = "SELECT * FROM WART_LIST WHERE IDm = '" & TUER_ID & "'"
            sqlConnection2.Open()
            reader2 = cmd2.ExecuteReader()

            While reader2.Read
                s = ""
                For i = 2 To 3
                    s = s & reader2(i).ToString & ";"
                Next
                s = s & reader2(4).ToString
                sw.WriteLine(s)
            End While
            sqlConnection2.Close()
            'End TUER
            s = "/TUER"
            sw.WriteLine(s)
        End While

        sqlConnection1.Close()

        'End AUF
        s = "/AUF"
        sw.WriteLine(s)

        sw.Close()
        sqlConnection1.Close()
        DispatchAUF = True

    End Function


    Function ExportAUF2(ByVal AUFRef As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        'Connection to Cadis DB Server
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim item As String = ""
        Dim Price As String = ""
        Dim TPrice As String = ""
        Dim s As String = ""
        Dim i As Integer = 0
        Dim AUF_ID As String = ""
        Dim TUER_ID As String = ""
        Dim KUNDE_ID As String = ""
        Dim Inventory As Boolean
        Dim AufDate As String = ""
        Dim AufArt As String = ""
        Dim AufDesc As String = ""
        Dim SerialDoor As String = ""
        Dim tDate As Date
        Dim Status As String = ""
        Dim AufNummer As String = ""


        'Defined output file
        Dim appendMode As Boolean = False
        Dim sw As New StreamWriter(My.Settings.sPathtoM3 & "\" & AUFRef & ".txt", appendMode)

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        'Get AUF line
        cmd.CommandText = "SELECT * FROM AUF WHERE ID = '" & AUFRef & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            AUF_ID = reader(0).ToString
            For i = 2 To 18
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(19).ToString
            sw.WriteLine(s)
            If reader(10).ToString = "91" Then
                Inventory = True
            Else
                Inventory = False
            End If
        End If

        'Get order date for small material
        tDate = reader(20).ToString
        AufDate = tDate.ToString("yyyyMMdd")
        AufArt = reader(7).ToString
        Status = Trim(reader(15).ToString)
        AufNummer = Trim(reader(6).ToString)

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get KUNDE line
        cmd.CommandText = "SELECT * FROM KUNDE WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            KUNDE_ID = reader(0).ToString
            s = ""
            For i = 2 To 10
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(11).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get OBJEKT line
        cmd.CommandText = "SELECT * FROM OBJEKT WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            For i = 2 To 20
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(21).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get TUER line
        cmd.CommandText = "SELECT * FROM TUER WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            TUER_ID = reader(0).ToString
            s = ""
            For i = 2 To 12
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(13).ToString
            sw.WriteLine(s)
        End If

        'Get serial number for small material
        SerialDoor = reader(4).ToString
        AufDesc = reader(3).ToString
        Dim tmp1 As String = ""
        Dim tmp2 As String = ""
        Dim tmp3 As String = ""

        sqlConnection1.Close()
        sqlConnection1.Open()

        'check if small material is needed
        Dim a As Boolean
        Dim b As Boolean

        CheckSM(SerialDoor, AufDate, AufArt, SerialDoor, tmp1, tmp2, tmp3, a, b)
        If Not Inventory Then
            cmd.CommandText = "SELECT ID FROM ETEIL WHERE IDm = '" & TUER_ID & "'"

            reader = cmd.ExecuteReader()
            If reader.Read Then
                'With material and not closed per phone
                If a And Status <> "T" Then
                    AddSM(TUER_ID, GetTotalPrice(TUER_ID, AufNummer), "930")
                End If

            Else
                'without material and not closed per phone
                If b And Status <> "T" Then
                    AddSM(TUER_ID, GetTotalPrice(TUER_ID, AufNummer), "930")
                End If

            End If
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get WART_LIST line
        cmd.CommandText = "SELECT * FROM WART_LIST WHERE IDm = '" & TUER_ID & "'"

        reader = cmd.ExecuteReader()
        While reader.Read
            s = ""
            For i = 2 To 3
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(4).ToString
            sw.WriteLine(s)
        End While

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get TAET line
        cmd.CommandText = "SELECT * FROM TAET WHERE IDm = '" & TUER_ID & "'"

        reader = cmd.ExecuteReader()
        While reader.Read
            s = ""
            For i = 2 To 5
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(6).ToString
            sw.WriteLine(s)
        End While

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get ETEIL line
        If Not Inventory Then
            cmd.CommandText = "SELECT * FROM ETEIL WHERE IDm = '" & TUER_ID & "'"

            reader = cmd.ExecuteReader()
            While reader.Read
                s = ""
                For i = 2 To 10
                    s = s & reader(i).ToString & ";"
                Next
                s = s & reader(11).ToString
                sw.WriteLine(s)
            End While
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'End TUER
        s = "/TUER"
        sw.WriteLine(s)

        'Get REPORT line
        cmd.CommandText = "SELECT * FROM REPORT WHERE IDm = '" & KUNDE_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then

            s = ""
            For i = 2 To 19
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(20).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()



        'Get SPESEN line
        cmd.CommandText = "SELECT * FROM SPESEN WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            For i = 2 To 6
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(7).ToString
            sw.WriteLine(s)
        End If

        'End AUF
        s = "/AUF"
        sw.WriteLine(s)

        sw.Close()
        sqlConnection1.Close()
        ExportAUF2 = True

    End Function

    Function ExportAUF1(ByVal AUFRef As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        'Connection to Cadis DB Server
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim item As String = ""
        Dim Price As String = ""
        Dim TPrice As String = ""
        Dim s As String = ""
        Dim i As Integer = 0
        Dim AUF_ID As String = ""
        Dim TUER_ID As String = ""
        Dim KUNDE_ID As String = ""
        Dim Inventory As Boolean
        Dim Status As String = ""

        'Defined output file
        Dim appendMode As Boolean = False
        Dim sw As New StreamWriter(My.Settings.sPathtoM3 & "\" & AUFRef & ".txt", appendMode)

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        'Get AUF line
        cmd.CommandText = "SELECT * FROM AUF WHERE ID = '" & AUFRef & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            AUF_ID = reader(0).ToString
            For i = 2 To 18
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(19).ToString
            sw.WriteLine(s)
            If reader(10).ToString = "91" Then
                Inventory = True
            Else
                Inventory = False
            End If
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get KUNDE line
        cmd.CommandText = "SELECT * FROM KUNDE WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            KUNDE_ID = reader(0).ToString
            s = ""
            For i = 2 To 10
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(11).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get OBJEKT line
        cmd.CommandText = "SELECT * FROM OBJEKT WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            For i = 2 To 20
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(21).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get TUER line
        cmd.CommandText = "SELECT * FROM TUER WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            TUER_ID = reader(0).ToString
            s = ""
            For i = 2 To 12
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(13).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get WART_LIST line
        cmd.CommandText = "SELECT * FROM WART_LIST WHERE IDm = '" & TUER_ID & "'"

        reader = cmd.ExecuteReader()
        While reader.Read
            s = ""
            For i = 2 To 3
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(4).ToString
            sw.WriteLine(s)
        End While

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get ETEIL line
        If Not Inventory Then
            cmd.CommandText = "SELECT * FROM ETEIL WHERE IDm = '" & TUER_ID & "'"

            reader = cmd.ExecuteReader()
            While reader.Read
                s = ""
                For i = 2 To 10
                    s = s & reader(i).ToString & ";"
                Next
                s = s & reader(11).ToString
                sw.WriteLine(s)
            End While
        End If

        'End TUER
        s = "/TUER"
        sw.WriteLine(s)

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get REPORT line
        cmd.CommandText = "SELECT * FROM REPORT WHERE IDm = '" & KUNDE_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then

            s = ""
            For i = 2 To 19
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(20).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get TAET line
        cmd.CommandText = "SELECT * FROM TAET WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            For i = 2 To 5
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(6).ToString
            sw.WriteLine(s)
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        'Get SPESEN line
        cmd.CommandText = "SELECT * FROM SPESEN WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            s = ""
            For i = 2 To 6
                s = s & reader(i).ToString & ";"
            Next
            s = s & reader(7).ToString
            sw.WriteLine(s)
        End If

        'End AUF
        s = "/AUF"
        sw.WriteLine(s)

        sw.Close()
        sqlConnection1.Close()
        ExportAUF1 = True

    End Function

    Function GetTotalPrice(ByVal ETEILRef As String, ByVal Ref As String) As String

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        'Connection to Cadis DB Server
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim item As String = ""
        Dim Price As String = ""
        Dim TPrice As String = ""
        Dim ID As String = ""
        Dim oDate As String = Format(Date.Today, "yyyyMMdd")

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        'Connection to M3
        Dim ConCM As OleDb.OleDbConnection
        Dim ReaderM3 As OleDb.OleDbDataReader
        Dim SQLString As String

        ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
        ConCM.Open()


        'Get all item for this door
        cmd.CommandText = "SELECT ID, Artikelnummer FROM ETEIL WHERE IDm = '" & ETEILRef & "'"

        reader = cmd.ExecuteReader()

        While reader.Read

            ID = Trim(reader(0).ToString)
            item = Trim(reader(1).ToString)

            Application.DoEvents()

            'Get price
            SQLString = "Select OMSAPR FROM SPRICH " & _
                        "INNER JOIN SIPRIL ON OJSTDT = OMSTDT AND OJCUNO = OMCUNO AND OJCUCD = OMCUCD AND OJPRS1 = OMPRS1 AND OJCONO = OMCONO " & _
                        "WHERE OJSTDT<=" & oDate & " AND OJLVDT>=" & oDate & " AND OMITNO='" & item & "' AND OJPRS1='" & My.Settings.JLD003_PriceList & "'"
            'SQLString = "select omsapr from sipril where omprs1 = 'S1' and omitno = '" & item & "'"
            Dim CMDM3 As New OleDb.OleDbCommand(SQLString, ConCM)
            ReaderM3 = CMDM3.ExecuteReader()
            If ReaderM3.Read Then
                Price = Trim(ReaderM3(0).ToString)

            Else
                'If no price found than try with the alias name of the item
                SQLString = "select mpitno from mitpop where mppopn = '" & item & "'"
                Dim CMDM31 As New OleDb.OleDbCommand(SQLString, ConCM)
                ReaderM3 = CMDM31.ExecuteReader()
                If ReaderM3.Read Then
                    item = Trim(ReaderM3(0).ToString)


                    SQLString = "Select OMSAPR FROM SPRICH " & _
                                 "INNER JOIN SIPRIL ON OJSTDT = OMSTDT AND OJCUNO = OMCUNO AND OJCUCD = OMCUCD AND OJPRS1 = OMPRS1 AND OJCONO = OMCONO " & _
                                 "WHERE OJSTDT<=" & oDate & " AND OJLVDT>=" & oDate & " AND OMITNO='" & item & "' AND OJPRS1='" & My.Settings.JLD003_PriceList & "'"
                    'SQLString = "select omsapr from sipril where omprs1 = 'S1' and omitno = '" & item & "'"
                    Dim CMDM32 As New OleDb.OleDbCommand(SQLString, ConCM)
                    ReaderM3 = CMDM32.ExecuteReader()
                    If ReaderM3.Read Then
                        Price = Trim(ReaderM3(0).ToString)
                    Else
                        If item <> "100-999003" Then
                            AddError(ID, "2", Ref, "Item price does not exist: " & item)
                            Price = "0"
                        End If
                    End If
                End If
            End If
            TPrice = Val(TPrice) + Val(Price)
        End While

        sqlConnection1.Close()
        ConCM.Close()
        GetTotalPrice = TPrice
    End Function

    Function CheckAUF(ByVal AUFRef As String, ByVal AUFNumber As String) As Boolean

        'Check if the order is ok

        'Connection to Cadis DB Server
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim item As String = ""
        Dim Price As String = ""
        Dim TPrice As String = ""
        Dim s As String = ""
        Dim i As Integer = 0
        Dim AUF_ID As String = ""
        Dim TUER_ID As String = ""
        Dim KUNDE_ID As String = ""

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        'Get AUF line
        cmd.CommandText = "SELECT * FROM AUF WHERE ID = '" & AUFRef & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            AUF_ID = reader(0).ToString
        End If
        sqlConnection1.Close()
        sqlConnection1.Open()


        'Check TUER line
        cmd.CommandText = "SELECT * FROM TUER WHERE IDm = '" & AUF_ID & "'"

        reader = cmd.ExecuteReader()
        If reader.Read Then
            TUER_ID = reader(0).ToString
            CheckSerialNumber(TUER_ID, AUFNumber, reader(4))
        End If
        sqlConnection1.Close()

        'Check ETEIL line
        CheckItem(TUER_ID, AUFNumber)

        CheckAUF = True

    End Function

    Function SQL_NNumber(ByVal sName As String) As String

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim s As String = ""

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = "SELECT NNumber FROM F0001 WHERE NName = '" & sName & "'"

        reader = cmd.ExecuteReader()

        If reader.Read Then
            s = reader(0).ToString
        Else
            s = "0"
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        s = Val(s) + 1

        If s = 1 Then
            cmd.CommandText = "INSERT INTO F0001 (NName, NNumber)   VALUES ('" & sName & "','1')"
        Else
            cmd.CommandText = "UPDATE F0001 SET NNumber = " & s & " WHERE NName = '" & sName & "'"
        End If

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception

        End Try

        sqlConnection1.Close()

        SQL_NNumber = s
    End Function

    Function GetActiveDirectoryUserGroups(ByVal UserName As String, ByVal GroupeSearch As String) As Boolean

        Dim search As New DirectorySearcher("")
        Dim groupCount As Int64
        Dim counter As Int64
        Dim GroupName As String
        Dim GroupArr As Array
        '  Dim DataToWriteGroups As String

        GetActiveDirectoryUserGroups = False

        Try

            search.Filter = "(&(!(userAccountControl:1.2.840.113556.1.4.803:=2))(objectCategory=user)(samaccountname=" + UserName.ToString.Trim + "))"
            search.PropertiesToLoad.Add("memberOf")


            Dim result As SearchResult = search.FindOne()

            If Not (IsNothing(result)) Then

                Try
                    groupCount = result.Properties("memberOf").Count
                Catch ex As NullReferenceException
                    groupCount = 0
                End Try

                If groupCount > 0 Then
                    For counter = 0 To groupCount - 1
                        GroupName = ""
                        GroupName = CStr(result.Properties("memberOf")(counter))
                        GroupArr = Split(GroupName, ",")
                        If Not (IsNothing(GroupArr(0))) Then
                            GroupName = Mid(GroupArr(0), 4, Len(GroupArr(0)) - 3)
                            If GroupeSearch = GroupName Then
                                GetActiveDirectoryUserGroups = True
                            End If
                        End If
                    Next
                End If

            End If
        Catch ex As Exception
            Exit Function
        Finally
            search.Dispose()
        End Try
    End Function

    Function ActivError(ByVal REF As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        'Connection to Cadis DB Server
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim ID As String = ""

        ActivError = False

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()


        'Get all item for this door
        cmd.CommandText = "SELECT * FROM F0003 WHERE REF = '" & REF & "' and CONF = 'N'"

        reader = cmd.ExecuteReader()

        If reader.Read Then
            ActivError = True
        End If

        sqlConnection1.Close()

    End Function

    Function CheckSerialNumber(ByVal AUFRef As String, ByVal AUF As String, ByVal Serie As String) As Boolean

        'Connection to M3
        Dim ConCM As OleDb.OleDbConnection
        Dim ReaderM3 As OleDb.OleDbDataReader
        Dim SQLString As String

        CheckSerialNumber = False

        ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
        ConCM.Open()

        'Check Lagerort und Item
        SQLString = "select SACONO from SINDIV where SAINNO = '" & Serie & "' "
        Dim CMDM As New OleDb.OleDbCommand(SQLString, ConCM)
        ReaderM3 = CMDM.ExecuteReader()
        If Not ReaderM3.Read Then
            CheckSerialNumber = True
            AddError(AUFRef, "3", AUF, "Serial number does not exist: " & Serie)
        End If

        'Check if serial number ist PIKETT
        If Trim(Serie).ToUpper = "PIKETT" Then
            CheckSerialNumber = True
            AddError(AUFRef, "3", AUF, "Serial number does not exist: " & Serie)
        End If

        'Check standart installation Date 
        SQLString = "SELECT SAINDA FROM SINDIV WHERE SAINNO = '" & Serie & "'"
        Dim CMDM3 As New OleDb.OleDbCommand(SQLString, ConCM)
        ReaderM3 = CMDM3.ExecuteReader()
        Dim InstDate As String = ""

        If ReaderM3.Read Then
            InstDate = Trim(ReaderM3(0).ToString)
            If InstDate.Length < 3 Then
                AddError(AUFRef, "6", AUF, "No installation date: " & Serie)
            End If

        End If

        ConCM.Close()

    End Function

    Function CheckItem(ByVal ETEILRef As String, ByVal Ref As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        'Connection to Cadis DB Server
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim ITNO As String = ""
        Dim WHLO As String = ""
        Dim CONO As String = ""
        Dim ID As String = ""

        CheckItem = False

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        'Connection to M3
        Dim ConCM As OleDb.OleDbConnection
        Dim ReaderM3 As OleDb.OleDbDataReader
        Dim SQLString As String

        ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
        ConCM.Open()


        'Get all item for this door
        cmd.CommandText = "SELECT ID, Artikelnummer, Lagerort FROM ETEIL WHERE IDm = '" & ETEILRef & "'"

        reader = cmd.ExecuteReader()

        While reader.Read

            ID = Trim(reader(0).ToString)
            ITNO = Trim(reader(1).ToString)
            WHLO = Trim(reader(2).ToString)

            Application.DoEvents()

            'Check Lagerort und Item
            SQLString = "select MBCONO from MITBAL where MBWHLO = '" & WHLO & "' and MBITNO = '" & ITNO & "'"
            Dim CMDM3 As New OleDb.OleDbCommand(SQLString, ConCM)
            ReaderM3 = CMDM3.ExecuteReader()
            If ReaderM3.Read Then
                CONO = Trim(ReaderM3(0).ToString)
                CheckItem = True
            Else
                'Check Lagerort und Item try with the alias name of the item
                SQLString = "select mpitno from mitpop where mppopn = '" & ITNO & "'"
                Dim CMDM31 As New OleDb.OleDbCommand(SQLString, ConCM)
                ReaderM3 = CMDM31.ExecuteReader()
                If ReaderM3.Read Then
                    ITNO = Trim(ReaderM3(0).ToString)

                    SQLString = "select MBCONO from MITBAL where MBWHLO = '" & WHLO & "' and MBITNO = '" & ITNO & "'"
                    Dim CMDM32 As New OleDb.OleDbCommand(SQLString, ConCM)
                    ReaderM3 = CMDM32.ExecuteReader()
                    If ReaderM3.Read Then
                        CONO = Trim(ReaderM3(0).ToString)
                    Else
                        AddError(ID, "1", Ref, "Item and or Location does not exist: " & ITNO & " / " & WHLO)
                        CheckItem = True
                    End If
                Else
                    AddError(ID, "1", Ref, "Item and or Location does not exist: " & ITNO & " / " & WHLO)
                    CheckItem = True

                End If
            End If

            'Check Sale flag
            Dim i As String
            SQLString = "select mmsale from mitmas where mmitno = '" & ITNO & "'"
            Dim CMDM As New OleDb.OleDbCommand(SQLString, ConCM)
            ReaderM3 = CMDM.ExecuteReader()
            If ReaderM3.Read Then
                i = Trim(ReaderM3(0).ToString)
                If i = "0" Then
                    CheckItem = True
                    AddError(ID, "4", Ref, "Item is not for sale: " & ITNO & " / " & WHLO)
                End If
            End If
        End While

        'Check Price
        GetTotalPrice(ETEILRef, Ref)

        sqlConnection1.Close()
        ConCM.Close()

    End Function

    Function AddError(ByVal IDm As String, ByVal ERRT As String, ByVal REF As String, ByVal DESC1 As String) As Boolean

        Dim sVar As Object = ""
        Dim ID_F0003 As String = ""
        Dim CONF As String = "N"

        AddError = False

        ID_F0003 = SQL_NNumber("ID_F0003")
        sVar = Split(ID_F0003 & ";" & IDm & ";" & ERRT & ";" & DESC1 & ";" & REF & ";" & CONF, ";")
        SQL_Insert(sVar, "F0003")

    End Function

    Function GetTableHeader(ByVal tName As String) As Boolean

        'Get all column name of a table with out ID

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim adapterTable As New SqlClient.SqlDataAdapter(cmd)

        Dim columnname As String = Nothing
        Dim columnNames(100) As String

        GetTableHeader = True

        cmd.CommandType = System.Data.CommandType.Text
        'Get table Header
        cmd.CommandText = "SELECT top 1 * from " & tName
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        Dim dtSet As New DataSet
        adapterTable.Fill(dtSet, tName)

        Dim dt As DataTable = dtSet.Tables(0)
        Dim dc As DataColumn
        For Each dc In dt.Columns
            columnname = columnname + dc.ColumnName + ","
        Next dc

        My.Settings.Item(tName) = columnname.Substring(0, columnname.Length - 1)

        sqlConnection1.Close()
    End Function

    Function AddMessage(ByVal DESC1 As String) As Boolean

        Dim sVar As Object = ""
        Dim ID_F0004 As String = ""
        Dim CONF As String = "N"


        Dim rName As String = My.User.Name
        Dim lSlash As Integer = rName.LastIndexOf("\")
        Dim fName As String = rName.Substring(lSlash + 1)

        AddMessage = False

        ID_F0004 = SQL_NNumber("ID_F0004")
        sVar = Split(ID_F0004 & ";" & DESC1 & ";" & fName, ";")
        SQL_Insert(sVar, "F0004")

    End Function

    Function GetAUFID(ByVal ID As String, ByVal ErrorTyp As String) As String

        'Get the AUF ID depend of the error typ

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim s As String = ""

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        If ErrorTyp = "1" Or ErrorTyp = "2" Or ErrorTyp = "4" Then
            cmd.CommandText = "select AUF.ID from ((dbo.AUF AUF INNER JOIN dbo.TUER TUER ON AUF.ID = TUER.IDm) " & _
                              "INNER JOIN dbo.ETEIL ETEIL ON TUER.ID = ETEIL.IDm) " & _
                              "where eteil.id = '" & ID & "'"
        End If

        If ErrorTyp = "3" Or ErrorTyp = "6" Then
            cmd.CommandText = "select AUF.ID from (dbo.AUF AUF INNER JOIN dbo.TUER TUER ON AUF.ID = TUER.IDm) " & _
                              "where TUER.ID = '" & ID & "'"
        End If

        reader = cmd.ExecuteReader()

        If reader.Read Then
            s = reader(0).ToString
        Else
            s = "0"
        End If

        sqlConnection1.Close()
        sqlConnection1.Open()

        GetAUFID = s
    End Function

    Function CheckSM(ByVal Serial As String, ByVal oDate As String, ByVal OrderType As String, ByVal OrderDesc As String, ByRef EndWarranty As String, ByRef EndExtWarranty As String, ByRef ContractType As String, ByRef Mat As Boolean, ByRef noMat As Boolean) As Boolean

        Dim InstDate As String = ""
        Dim dInstDate As Date
        Dim provider As CultureInfo = CultureInfo.InvariantCulture
        Dim i As String
        '  Dim dExWarranty As Date
        Dim dOrder As Date = Date.ParseExact(oDate, "yyyyMMdd", provider)
        Dim dEndWarranty As Date = Date.ParseExact("19000101", "yyyyMMdd", provider)
        Dim dEndExtWarranty As Date = Date.ParseExact("19000101", "yyyyMMdd", provider)

        CheckSM = False

        'Connection to M3
        Dim ConCM As OleDb.OleDbConnection
        Dim ReaderM3 As OleDb.OleDbDataReader
        Dim SQLString As String

        ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
        ConCM.Open()


        'Get standart installation Date 
        SQLString = "SELECT SAINDA FROM SINDIV WHERE SAINNO = '" & Serial & "'"
        Dim CMDM3 As New OleDb.OleDbCommand(SQLString, ConCM)
        ReaderM3 = CMDM3.ExecuteReader()

        If ReaderM3.Read Then
            InstDate = Trim(ReaderM3(0).ToString)
            dInstDate = Date.ParseExact(InstDate, "yyyyMMdd", provider)
            dEndWarranty = dInstDate.AddYears(1)
        Else
            dInstDate = Date.ParseExact("19000101", "yyyyMMdd", provider)
            dEndWarranty = Date.ParseExact("19000101", "yyyyMMdd", provider)

        End If

        'Get extended warranty 
        SQLString = "SELECT OLYEA1 FROM SMWCON WHERE OLIDE1 = '" & Serial & "'"
        Dim CMD1M3 As New OleDb.OleDbCommand(SQLString, ConCM)
        ReaderM3 = CMD1M3.ExecuteReader()
        If ReaderM3.Read Then
            i = Trim(ReaderM3(0).ToString)
            'dExWarranty = Date.ParseExact(InstDate, "yyyyMMdd", provider)
            dEndExtWarranty = dInstDate.AddDays(365 * Val(i))
        End If

        'Get Contract type

        Format(oDate, "yyyyMMdd")

        SQLString = "SELECT CCAGTP, CCAGRS, CCCUPL, CCVADF, CCVADT, CCINNO " & _
                    "FROM SALINE INNER JOIN SAHEAD ON (CCCTNO = CACTNO) AND (CCCONO = CACONO) " & _
                    "Where CCINNO = '" & Serial & "' and caaotp = 'A10' AND CCVADF <= '" & oDate & "' AND CCVADT >= '" & oDate & "'"
        Dim CMD2M3 As New OleDb.OleDbCommand(SQLString, ConCM)
        ReaderM3 = CMD2M3.ExecuteReader()
        If ReaderM3.Read Then
            ContractType = Trim(ReaderM3(0).ToString)
        End If

        ConCM.Close()

        'check if small material can be added



        Dim condMat = False
        Dim condNoMat = False
        Dim sContract As Boolean = False
        Dim bContract As Boolean = False
        Dim cContract As Boolean = False

        If ContractType = "110" Or _
           ContractType = "130" Or _
           ContractType = "140" Or _
           ContractType = "150" Then
            bContract = True
        End If

        If ContractType = "110" Then
            cContract = True
        End If

        If ContractType = "100" Then
            sContract = True
        End If

        '1- Check with warranty
        If dOrder < dEndWarranty Or dOrder < dEndExtWarranty Then
            condMat = False
            condNoMat = False
        Else
            '2- check big warranty
            If bContract Then
                If OrderType = "SV" Or OrderType = "WM" Then
                    condMat = True
                    condNoMat = False
                End If
                If OrderType = "NO" Then
                    If OrderDesc = "PIKETT" Then
                        If cContract Then
                            condMat = True
                            condNoMat = True
                        Else
                            condMat = True
                            condNoMat = False
                        End If
                    Else
                        condMat = False
                        condNoMat = False
                    End If
                End If
            Else
                If sContract Then
                    '3- check small contract
                    If OrderType = "SV" Then
                        condMat = True
                        condNoMat = True
                    End If
                    If OrderType = "WM" Then
                        condMat = True
                        condNoMat = False
                    End If
                    If OrderType = "NO" Then
                        If OrderDesc = "PIKETT" Then
                            condMat = True
                            condNoMat = True
                        Else
                            condMat = False
                            condNoMat = False
                        End If
                    End If
                Else
                    If OrderType = "SV" Or OrderType = "WM" Then
                        condMat = True
                        condNoMat = True
                    End If
                    If OrderType = "NO" Then
                        If OrderDesc = "PIKETT" Then
                            condMat = True
                            condNoMat = True
                        Else
                            condMat = False
                            condNoMat = False
                        End If
                    End If
                End If
            End If
        End If
        EndWarranty = dEndWarranty
        EndExtWarranty = dEndExtWarranty
        Mat = condMat
        noMat = condNoMat
        CheckSM = True

    End Function

    Function TranferInventory(ByVal SDSTNB As String, ByVal IDm_ETEIL As String) As Boolean

        'Connection to Cadis DB Server
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim sqlConnection2 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim cmd1 As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim reader1 As SqlClient.SqlDataReader = Nothing

        Dim ID As String = ""
        Dim TMP As String = ""
        Dim sVar As Object
        Dim ID_F0005 As String

        Dim SDITNO As String = ""
        Dim SDWHLO As String = ""
        Dim SDBANO As String = ""
        Dim SDSTQI As String = ""
        Dim SDWHSL As String = ""
        Dim SDUOM1 As String = ""
        Dim SDSTAT As String = "01"
        Dim SDDESC As String = ""
        Dim SDDESC1 As String = ""
        Dim SDDESC2 As String = ""

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        cmd1.CommandType = System.Data.CommandType.Text
        cmd1.Connection = sqlConnection2
        sqlConnection1.Open()

        'Connection to M3
        Dim ConCM As OleDb.OleDbConnection
        Dim ReaderM3 As OleDb.OleDbDataReader
        Dim SQLString As String

        ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
        ConCM.Open()

        cmd.CommandText = " DELETE F0005 WHERE SDSTNB = '" & SDSTNB & "'"
        reader = cmd.ExecuteReader()
        reader.Read()
        sqlConnection1.Close()

        sqlConnection1.Open()
        cmd.CommandText = "SELECT count(*) " & _
                          "FROM ETEIL " & _
                          "Where IDm = '" & IDm_ETEIL & "'"
        reader = cmd.ExecuteReader()

        If reader.Read Then
            Form6.ProgressBar1.Maximum = reader(0).ToString
        End If
        sqlConnection1.Close()

        sqlConnection1.Open()
        'Get all item for the inventory
        cmd.CommandText = "SELECT Artikelnummer, Seriennummer, Lagerort, Sum(cast(Transaktionsmenge as real)) " & _
                          "AS SummevonTransaktionsmenge, Transaktionseinheit, IDm " & _
                          "FROM ETEIL " & _
                          "GROUP BY Artikelnummer, Seriennummer, Lagerort, Transaktionseinheit, IDm " & _
                          "HAVING IDm = '" & IDm_ETEIL & "'"

        reader = cmd.ExecuteReader()

        'Set process bar 
        Form6.ProgressBar1.Visible = True
        Form6.ProgressBar1.Minimum = 0
        Form6.ProgressBar1.Value = 0
        Form6.ProgressBar1.Step = 1


        While reader.Read

            SDITNO = Trim(reader(0).ToString)
            SDBANO = Trim(reader(1).ToString)
            SDWHLO = Trim(reader(2).ToString)
            SDSTQI = Trim(reader(3).ToString)
            SDUOM1 = Trim(reader(4).ToString)

            Application.DoEvents()
            Form6.ProgressBar1.PerformStep()


            'check if the item number is the new one. If not get the new one
            SQLString = "select mmitno from mitmas where mmitno = '" & SDITNO & "'"
            Dim CMDM3 As New OleDb.OleDbCommand(SQLString, ConCM)
            ReaderM3 = CMDM3.ExecuteReader()
            If ReaderM3.Read Then
                TMP = Trim(ReaderM3(0).ToString)
            Else
                'If not found search with the alias
                SQLString = "select mpitno from mitpop where mppopn = '" & SDITNO & "'"
                Dim CMDM31 As New OleDb.OleDbCommand(SQLString, ConCM)
                ReaderM3 = CMDM31.ExecuteReader()
                If ReaderM3.Read Then
                    SDITNO = Trim(ReaderM3(0).ToString)
                End If
            End If

            sqlConnection2.Open()

            'Check if already in F0005. If yes then add the quatity else insert
            'sqlConnection1.Open()
            cmd1.CommandText = "SELECT ID, SDSTNB, SDWHLO, SDWHSL, SDITNO, SDBANO, SDSTQI " & _
                              "FROM F0005 " & _
                              "Where SDSTNB = '" & SDSTNB & "' AND SDWHLO = '" & SDWHLO & "' AND SDWHSL = '" & SDWHSL & "' AND " & _
                              "SDITNO = '" & SDITNO & "' AND SDBANO = '" & SDBANO & "' "
            reader1 = cmd1.ExecuteReader

            If reader1.Read Then
                ID_F0005 = Trim(reader1(0).ToString)
                SDSTQI = Val(SDSTQI) + Val(Trim(reader1(6).ToString))
                'Update quantity
                sqlConnection2.Close()
                sqlConnection2.Open()
                cmd1.CommandText = "UPDATE F0005 SET SDSTQI = '" & SDSTQI & "' " & _
                                  "WHERE ID = '" & ID_F0005 & "' "
                reader1 = cmd1.ExecuteReader

            Else
                'Insert to F0005

                ID_F0005 = SQL_NNumber("ID_F0005")

                sVar = Split(ID_F0005 & ";" & SDSTNB & ";" & SDWHLO & ";" & SDWHSL & ";" & SDITNO & ";" & SDBANO & ";" & SDSTQI & ";" & SDUOM1 & ";" & SDSTAT & ";" & SDDESC & ";" & SDDESC1 & ";" & SDDESC2, ";")
                SQL_Insert(sVar, "F0005")

            End If

            sqlConnection2.Close()

        End While

        sqlConnection1.Close()
        ConCM.Close()

        Form6.ProgressBar1.Visible = False
        TranferInventory = True
    End Function

    Function LoadInventory(ByVal SDSTNB As String, ByVal SDSTDI As String, ByVal SDSTTM As String) As Boolean

        'Connection to Cadis DB Server
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim sqlConnection2 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim sqlConnection3 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim cmd1 As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim reader1 As SqlClient.SqlDataReader = Nothing
        Dim reader2 As SqlClient.SqlDataReader = Nothing

        Dim ID As String = ""
        Dim TMP As String = ""

        Dim SDITNO As String = ""
        Dim SDWHLO As String = ""
        Dim SDBANO As String = ""
        Dim SDSTQI As String = ""
        Dim DSWHSL As String = "02-00"
        Dim SDUOM1 As String = ""
        Dim SDSTAT As String = "01"
        Dim SDDESC As String = ""
        Dim SDDESC1 As String = ""
        Dim SDDESC2 As String = ""
        Dim SDWHSL As String = ""
        Dim SDSTRN As String = ""
        Dim ID_F0005 As String = ""
        Dim SDSTRNTemp As String = ""

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd1.CommandType = System.Data.CommandType.Text
        cmd1.Connection = sqlConnection3
        sqlConnection3.Open()


        'Connection to M3
        Dim ConCM As OleDb.OleDbConnection
        Dim ReaderM3 As OleDb.OleDbDataReader
        Dim SQLString As String

        ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
        ConCM.Open()

        'Get the next line number
        SQLString = "select sdstrn from mittkd where sdstnb = '" & SDSTNB & "' group by sdstrn order by sdstrn desc "
        Dim CMD4M3 As New OleDb.OleDbCommand(SQLString, ConCM)
        ReaderM3 = CMD4M3.ExecuteReader()

        If ReaderM3.Read Then SDSTRNTemp = ReaderM3(0) + 10


        'Set process bar 
        Form6.ProgressBar2.Visible = True
        Form6.ProgressBar2.Minimum = 0
        Form6.ProgressBar2.Value = 0
        Form6.ProgressBar2.Step = 1
        cmd1.CommandText = "SELECT count(*)FROM F0005 WHERE SDSTNB = '" & SDSTNB & "'"
        reader2 = cmd1.ExecuteReader()
        If reader2.Read Then Form6.ProgressBar2.Maximum = reader2(0)



        'Get all item for the inventory
        cmd.CommandText = "SELECT SDSTNB, SDWHLO, SDWHSL, SDITNO, SDBANO, SDSTQI, ID " & _
                          "FROM F0005 WHERE SDSTNB = '" & SDSTNB & "'"
        reader = cmd.ExecuteReader()

        While reader.Read

            SDSTNB = Trim(reader(0).ToString)
            SDWHLO = Trim(reader(1).ToString)
            SDWHSL = Trim(reader(2).ToString)
            SDITNO = Trim(reader(3).ToString)
            SDBANO = Trim(reader(4).ToString)
            SDSTQI = Trim(reader(5).ToString)
            ID_F0005 = Trim(reader(6).ToString)


            Application.DoEvents()
            Form6.ProgressBar2.PerformStep()



            'check if the item number is in the invetory file
            SQLString = "SELECT SDSTRN FROM MITTKD WHERE SDITNO = '" & SDITNO & "' and sdbano = '" & SDBANO & "' AND SDSTNB = '" & SDSTNB & "'"
            Dim CMDM3 As New OleDb.OleDbCommand(SQLString, ConCM)
            ReaderM3 = CMDM3.ExecuteReader()

            If ReaderM3.Read Then

                'If set exit than update will run
                SDSTRN = Trim(ReaderM3(0).ToString)

                SQLString = "UPDATE MITTKD SET SDSTQI = " & SDSTQI & ", SDSTDI = " & SDSTDI & ", SDSTTM = " & SDSTTM & ", SDRESP = '101-JLD', SDSTAG = 1 " & _
                            "WHERE SDCONO = 1 and SDSTRN = " & SDSTRN & " and SDSTNB = " & SDSTNB & ""

                Dim CMD1M3 As New OleDb.OleDbCommand(SQLString, ConCM)
                ReaderM3 = CMD1M3.ExecuteReader()
                ReaderM3.Read()

                'Update the source table
                'cmd2.CommandText = "UPDATE F0005 SET SDSTAT = '10' SDDESC = 'Loaded to MNS300" & _
                '  "WHERE ID = '" & ID_F0005 & "'"
                'reader1 = cmd2.ExecuteReader()
            Else
                'Add a cicle count line 
                SDSTRNTemp = Val(SDSTRNTemp) + 1

                If SDBANO.Length > 8 Then SDBANO = SDBANO.Substring(1, 8)

                SQLString = "INSERT INTO MITTKD(SDCONO, SDWHLO, SDSTNB, " & _
                                               "SDSTRN, SDWHSL, SDSLTP, " & _
                                               "SDBANO, SDCAMU, SDREPN, " & _
                                               "SDITNO, SDSTQI, SDSAQI, " & _
                                               "SDRESP, SDSTDI, SDSTTM, " & _
                                               "SDSTNR, SDSTQC, SDSTAG, " & _
                                               "SDSTQR, SDSAQR, SDSTAC, " & _
                                               "SDBREF, SDBRE2, SDBREM, " & _
                                               "SDSTBT, SDCAWE, SDTXID, " & _
                                               "SDRGDT, SDRGTM, SDLMDT, " & _
                                               "SDCHNO, SDCHID) " & _
                                       "VALUES (1, '" & SDWHLO & "', " & SDSTNB & " , " & _
                                               "" & SDSTRNTemp & ", '02-00', ' ', " & _
                                               "'" & SDBANO & "', ' ', 0, " & _
                                               "'" & SDITNO & "', " & SDSTQI & ", 0, " & _
                                               "'101-JLD', " & SDSTDI & ", " & SDSTTM & ", " & _
                                               "0, 0, 1, " & _
                                               "0, 0, '2', " & _
                                               "' ', ' ', ' ', " & _
                                               "0, 0, 0, " & _
                                               "" & SDSTDI & ", " & SDSTTM & ", " & SDSTDI & ", " & _
                                               "0, '101-JLD')"
                Dim CMD2M3 As New OleDb.OleDbCommand(SQLString, ConCM)
                ReaderM3 = CMD2M3.ExecuteReader()
                ReaderM3.Read()


            End If

        End While

        SQLString = "update mittkd set sdstag = 1 where sdstnb = '" & SDSTNB & "'"

        Dim CMD3M3 As New OleDb.OleDbCommand(SQLString, ConCM)
        ReaderM3 = CMD3M3.ExecuteReader()
        ReaderM3.Read()

        sqlConnection1.Close()
        ConCM.Close()
        Form6.ProgressBar2.Visible = False
        LoadInventory = True
    End Function

    Public Function Statistics(ByVal JLJBST As String, ByVal JLSOTP As String, ByVal Title As String, ByVal xAxis As String, ByVal yAxis As String)

        Dim i As Integer
        Dim max As Integer = 0
        Dim dFrom As String = ""
        Dim dTo As String = ""

        ' Erstellen einer Collection und 
        ' Hinzufügen von Werten.
        Dim col As Collection = New Collection()
        col.Add("")

        ' Erstellen einer Arrayliste, die als Adapter 
        ' für die Collection dient.
        Dim al As ArrayList = ArrayList.Adapter(col)
        For i = 0 To 2000
            al.Add(0)
            al.Item(i) = 0
        Next


        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing

        'Get max count
        cmd.CommandText = "select COUNT(*) from f0006 where JLJBST = '" & JLJBST & "'  and jlsotp = '" & JLSOTP & "' "
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()
        reader = cmd.ExecuteReader()
        If reader.Read Then max = Val(reader(0))
        sqlConnection1.Close()

        'Get Data
        sqlConnection1.Open()
        cmd.CommandText = "select top 2000 JLCOUNT, TimeDate from f0006 where JLJBST = '" & JLJBST & "'  and jlsotp = '" & JLSOTP & "' order by TimeDate asc"
        cmd.Connection = sqlConnection1

        reader = cmd.ExecuteReader()
        i = 0
        While reader.Read
            If i = 0 Then dFrom = reader(1).ToString
            al.Item(i) = Int(reader(0).ToString)
            dTo = reader(1).ToString
            i = i + 1
        End While

        sqlConnection1.Close()

        Dim zgc As ZedGraphControl
        zgc = Graf.zg1

        zgc.ResetText()

        Dim myPane As GraphPane = zgc.GraphPane

        ' Set the titles and axis labels
        myPane.Title.Text = Title
        myPane.XAxis.Title.Text = "From   " & dFrom & "    to    " & dTo
        myPane.XAxis.Scale.Min = 1 - max
        myPane.XAxis.Scale.Max = 0
        myPane.YAxis.Scale.Min = 0
        myPane.YAxis.Title.Text = yAxis
        myPane.Y2Axis.Scale.Min = 0
        myPane.Y2Axis.IsVisible = True


        ' Make up some data points from the Sine function
        Dim list = New PointPairList()
        Dim x As Double, y As Double
        For x = 0 To max - 1
            y = al.Item(x)
            list.Add(1 - max + x, y)
        Next x

        ' Generate a blue curve with circle symbols, and "My Curve 2" in the legend
        myPane.CurveList.Clear()

        Dim myCurve As LineItem = myPane.AddCurve("", list, Color.Blue, 9)

        ' Fill the area under the curve with a white-red gradient at 45 degrees

        myCurve.Line.IsSmooth = True
        myCurve.Line.Fill = New Fill(Color.White, Color.Blue, 45.0F)

        myPane.XAxis.Type = AxisType.Linear

        ' Make the symbols opaque by filling them with white
        myCurve.Symbol.Fill = New Fill(Color.White)

        myCurve.Symbol.IsVisible = False

        ' Fill the axis background with a color gradient
        myPane.Chart.Fill = New Fill(Color.White, Color.LightGoldenrodYellow, 45.0F)

        ' Fill the pane background with a color gradient
        myPane.Fill = New Fill(Color.White, Color.FromArgb(220, 220, 255), 45.0F)

        ' Calculate the Axis Scale Ranges
        zgc.AxisChange()


        'SetSize()
        zgc.Visible = True
        zgc.Refresh()

        Graf.Show()

        Statistics = True
    End Function

    Public Function Statistics1(ByVal JLJBST As String, ByVal JLSOTP As String, ByVal Title As String, ByVal xAxis As String, ByVal yAxis As String)

        Dim i As Integer
        Dim max As Integer = 0
        Dim dFrom As String = ""
        Dim dTo As String = ""
        Dim x1() As Double


        ' Erstellen einer Collection und 
        ' Hinzufügen von Werten.
        Dim col As Collection = New Collection()
        col.Add("")
        Dim col1 As Collection = New Collection()
        col.Add("")

        ' Erstellen einer Arrayliste, die als Adapter 
        ' für die Collection dient.
        Dim al As ArrayList = ArrayList.Adapter(col)
        For i = 0 To 2000
            al.Add(0)
            al.Item(i) = 0
        Next

        Dim al1 As ArrayList = ArrayList.Adapter(col1)
        For i = 0 To 2000
            al1.Add(0)
            al1.Item(i) = 0
        Next


        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing

        'Get max count
        cmd.CommandText = "select COUNT(*) from f0009 where JLJBST = '" & JLJBST & "'  and jlsotp = '" & JLSOTP & "' and jldays = '0'"
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()
        reader = cmd.ExecuteReader()
        If reader.Read Then max = Val(reader(0))
        sqlConnection1.Close()

        If max > 2000 Then max = 2000

        ReDim x1(max)
        Dim yyyy As Integer
        Dim MM As Integer
        Dim DD As Integer
        Dim hh As Integer
        Dim min As Integer
        Dim ms As Integer
        Dim dstart As Double
        Dim dstop As Double



        'Get Data
        sqlConnection1.Open()
        cmd.CommandText = "select top 2000 JLCOUNT, TimeDate , year(timedate), month(timedate), day(timedate), datepart(hour, timedate),datepart(minute, timedate),datepart(second, timedate) from f0009 where JLJBST = '" & JLJBST & "' and jldays = '0' and jlsotp = '" & JLSOTP & "' order by TimeDate DESC"
        cmd.Connection = sqlConnection1

        reader = cmd.ExecuteReader()
        i = 0
        While reader.Read
            If i = 0 Then dFrom = reader(1).ToString
            al.Item(i) = Int(reader(0).ToString)
            dTo = reader(1).ToString

            yyyy = Int(reader(2).ToString)
            MM = Int(reader(3).ToString)
            DD = Int(reader(4).ToString)
            hh = Int(reader(5).ToString)
            min = Int(reader(6).ToString)
            ms = 0
            '********************************************************************************************
            x1(i) = New XDate(yyyy, MM, DD, hh, min, ms).XLDate
            i = i + 1
        End While
        dstop = x1(0)
        If i <> 0 Then dstart = x1(i - 1)



        sqlConnection1.Close()

        'Get Data
        sqlConnection1.Open()
        cmd.CommandText = "select top 2000 JLCOUNT, TimeDate from f0009 where JLJBST = '" & JLJBST & "' and jldays = '5' and jlsotp = '" & JLSOTP & "' order by TimeDate DESC"
        cmd.Connection = sqlConnection1

        reader = cmd.ExecuteReader()
        i = 0
        While reader.Read
            If i = 0 Then dTo = reader(1).ToString
            al1.Item(i) = Int(reader(0).ToString)
            dFrom = reader(1).ToString
            i = i + 1
        End While

        sqlConnection1.Close()

        Dim zgc As ZedGraphControl
        zgc = Graf.zg1

        zgc.ResetText()

        Dim myPane As GraphPane = zgc.GraphPane

        ' Set the titles and axis labels
        myPane.Title.Text = Title
        myPane.XAxis.Title.Text = "From   " & dFrom & "    to    " & dTo
        myPane.XAxis.Scale.Min = dstart
        myPane.XAxis.Scale.Max = dstop
        myPane.YAxis.Scale.Min = 0
        myPane.YAxis.Title.Text = yAxis

        myPane.Y2Axis.Scale.Min = 0
        myPane.Y2Axis.IsVisible = True

        ' Make up some data points from the Sine function
        Dim list = New PointPairList()
        Dim list1 = New PointPairList()
        Dim x As Double, y As Double, y1 As Double
        For x = 0 To max - 1
            y = al.Item(x)
            y1 = al1.Item(x)
            list.Add(x1(x), y)
            list1.Add(x1(x), y1 + y)
            'list.Add(1 - max + x, y)
            'list1.Add(1 - max + x, y1 + y)
        Next x

        ' Generate a blue curve with circle symbols, and "My Curve 2" in the legend
        myPane.CurveList.Clear()

        '   Dim myCurve As CurveItem = myPane.AddBar("SV", x1, y1, Color.Green)
        '  Dim myCurve1 As CurveItem = myPane.AddBar("WM", x2, y2, Color.Yellow)
        ' Dim myCurve2 As CurveItem = myPane.AddBar("NO", x3, y3, Color.Blue)

        myPane.XAxis.Type = AxisType.Date



        Dim myCurve As LineItem = myPane.AddCurve("New", list, Color.Blue, 9)
        Dim myCurve1 As LineItem = myPane.AddCurve("6 days old or more", list1, Color.Red, 9)

        ' Fill the area under the curve with a white-red gradient at 45 degrees

        myCurve.Line.IsSmooth = True
        myCurve1.Line.IsSmooth = True
        myCurve.Line.Fill = New Fill(Color.White, Color.Blue, 45.0F)
        myCurve1.Line.Fill = New Fill(Color.White, Color.Red, 45.0F)

        'myPane.XAxis.Type = AxisType.Linear

        ' Make the symbols opaque by filling them with white
        myCurve.Symbol.Fill = New Fill(Color.White)

        myCurve.Symbol.IsVisible = False
        myCurve1.Symbol.IsVisible = False

        ' Fill the axis background with a color gradient
        myPane.Chart.Fill = New Fill(Color.White, Color.LightGoldenrodYellow, 45.0F)

        ' Fill the pane background with a color gradient
        myPane.Fill = New Fill(Color.White, Color.FromArgb(220, 220, 255), 45.0F)

        ' Calculate the Axis Scale Ranges
        zgc.AxisChange()

        'SetSize()
        zgc.Visible = True
        zgc.Refresh()

        Graf.Show()

        Statistics1 = True
    End Function

    Public Function Statistics2()

        Dim i As Integer
        Dim max As Integer = 0
        Dim max1 As Integer = 0
        Dim max2 As Integer = 0
        Dim max3 As Integer = 0
        Dim dFrom As String = ""
        Dim dTo As String = ""

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim reader1 As SqlClient.SqlDataReader = Nothing

        '****************************************************************
        'Get Data SV
        '****************************************************************
        sqlConnection1.Open()
        cmd.CommandText = "select Year(TimeDate), Month(TimeDate), day(TimeDate), count(*) as Anzahl from AUF where Auftragsart='SV' and Direction = 'R' " & _
                          "group by Month(TimeDate),   day(TimeDate),Year(TimeDate) " & _
                          "order by   Year(TimeDate), Month(TimeDate),  day(TimeDate) asc"
        cmd.Connection = sqlConnection1
        Dim x1() As Double
        Dim y1() As Double
        'Get # of elements
        reader = cmd.ExecuteReader()
        max1 = 0
        While reader.Read
            max1 = max1 + 1
        End While
        ReDim x1(max1)
        ReDim y1(max1)
        reader.Close()

        reader = cmd.ExecuteReader()
        i = 0
        While reader.Read
            y1(i) = Int(reader(3).ToString)
            x1(i) = New XDate(Int(reader(0).ToString), Int(reader(1).ToString), Int(reader(2).ToString)).XLDate
            i = i + 1
        End While
        reader.Close()

        '****************************************************************
        'Get Data WM
        '****************************************************************
        cmd.CommandText = "select Year(TimeDate), Month(TimeDate), day(TimeDate), count(*) as Anzahl from AUF where Auftragsart='WM' and Direction = 'R' " & _
                  "group by Month(TimeDate),   day(TimeDate),Year(TimeDate) " & _
                  "order by   Year(TimeDate), Month(TimeDate),  day(TimeDate) DESC"
        cmd.Connection = sqlConnection1
        Dim x2() As Double
        Dim y2() As Double
        'Get # of elements
        reader = cmd.ExecuteReader()
        max2 = 0
        While reader.Read
            max2 = max2 + 1
        End While
        ReDim x2(max2)
        ReDim y2(max2)
        reader.Close()

        reader = cmd.ExecuteReader()
        i = 0
        While reader.Read
            y2(i) = Int(reader(3).ToString)
            x2(i) = New XDate(Int(reader(0).ToString), Int(reader(1).ToString), Int(reader(2).ToString)).XLDate
            i = i + 1
        End While
        reader.Close()

        '****************************************************************
        'Get Data NO
        '****************************************************************
        cmd.CommandText = "select Year(TimeDate), Month(TimeDate), day(TimeDate), count(*) as Anzahl from AUF where Auftragsart='NO' and Direction = 'R' " & _
                  "group by Month(TimeDate),   day(TimeDate),Year(TimeDate) " & _
                  "order by  Year(TimeDate), Month(TimeDate),  day(TimeDate) DESC"
        cmd.Connection = sqlConnection1
        Dim x3() As Double
        Dim y3() As Double
        'Get # of elements
        reader = cmd.ExecuteReader()
        max3 = 0
        While reader.Read
            max3 = max3 + 1
        End While
        ReDim x3(max3)
        ReDim y3(max3)
        reader.Close()

        reader = cmd.ExecuteReader()
        i = 0
        While reader.Read
            y3(i) = Int(reader(3).ToString)
            x3(i) = New XDate(Int(reader(0).ToString), Int(reader(1).ToString), Int(reader(2).ToString)).XLDate
            i = i + 1
        End While
        reader.Close()
        sqlConnection1.Close()

        Dim zgc As ZedGraphControl
        zgc = Graf.zg1
        zgc.ResetText()

        Dim myPane As GraphPane = zgc.GraphPane

        myPane.CurveList.Clear()

        ' Set the titles and axis labels
        myPane.Title.Text = "# of returned orders from Cadis"
        myPane.XAxis.Title.Text = ""
        myPane.XAxis.Scale.Min = x1(0) - 1

        'myPane.Y2Axis.Scale.Min = 0
        myPane.Y2Axis.IsVisible = True

        max = x1(max1 - 1)
        If x2(max2 - 1) > max Then max = x2(max2 - 1)
        If x3(max3 - 1) > max Then max = x3(max3 - 1)

        myPane.XAxis.Scale.Max = max + 1
        myPane.YAxis.Title.Text = "# of orders"

        ' Generate bars
        'myPane.CurveList.Clear()
        Dim myCurve As BarItem = myPane.AddBar("SV", x1, y1, Color.Green)
        Dim myCurve1 As BarItem = myPane.AddBar("WM", x2, y2, Color.Yellow)
        Dim myCurve2 As BarItem = myPane.AddBar("NO", x3, y3, Color.Blue)

        myPane.XAxis.Type = AxisType.Date

        ' Fill the axis background with a color gradient
        myPane.Chart.Fill = New Fill(Color.White, Color.LightGoldenrodYellow, 45.0F)

        ' Fill the pane background with a color gradient
        myPane.Fill = New Fill(Color.White, Color.FromArgb(220, 220, 255), 45.0F)

        zgc.AxisChange()

        zgc.Visible = True
        zgc.Refresh()

        Graf.Show()

        Statistics2 = True
    End Function

    Public Function StatisticsTech(ByVal JDJBST As String, ByVal JDSOTP As String, ByVal JDTECH As String, ByVal Title As String, ByVal xAxis As String, ByVal yAxis As String)

        Dim i As Integer
        Dim max As Integer = 0
        Dim dFrom As String = ""
        Dim dTo As String = ""

        Dim yyyy As Integer
        Dim MM As Integer
        Dim DD As Integer
        Dim hh As Integer
        Dim min As Integer
        Dim ms As Integer
        Dim dstart As Double
        Dim dstop As Double
        Dim x1() As Double


        ' Erstellen einer Collection und 
        ' Hinzufügen von Werten.
        Dim col As Collection = New Collection()
        col.Add("")
        Dim col1 As Collection = New Collection()
        col1.Add("")

        ' Erstellen einer Arrayliste, die als Adapter 
        ' für die Collection dient.
        Dim al As ArrayList = ArrayList.Adapter(col)
        For i = 0 To 2000
            al.Add(0)
            al.Item(i) = 0
        Next

        Dim al1 As ArrayList = ArrayList.Adapter(col1)
        For i = 0 To 2000
            al1.Add(0)
            al1.Item(i) = 0
        Next



        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing

        'Get max count
        cmd.CommandText = "select COUNT(*) from f0008 where JDJBST = '" & JDJBST & "'  and jddays = '0' and jDsotp = '" & JDSOTP & "'  and JDTECH = '" & JDTECH & "' "
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()
        reader = cmd.ExecuteReader()
        If reader.Read Then max = Val(reader(0))
        sqlConnection1.Close()

        If max > 2000 Then max = 2000

        ReDim x1(max)

        'Get Data last 5 days
        sqlConnection1.Open()
        cmd.CommandText = "select top 2000 JDCOUNT, TimeDate, year(timedate), month(timedate), day(timedate), datepart(hour, timedate),datepart(minute, timedate),datepart(second, timedate) from f0008 where jddays = '0' and JDJBST = '" & JDJBST & "'  and jDsotp = '" & JDSOTP & "' and JDTECH = '" & JDTECH & "' order by TimeDate DESC"
        cmd.Connection = sqlConnection1

        reader = cmd.ExecuteReader()
        i = 0
        While reader.Read
            If i = 0 Then dTo = reader(1).ToString
            al.Item(i) = Int(reader(0).ToString)
            dFrom = reader(1).ToString
            yyyy = Int(reader(2).ToString)
            MM = Int(reader(3).ToString)
            DD = Int(reader(4).ToString)
            hh = Int(reader(5).ToString)
            min = Int(reader(6).ToString)
            ms = 0
            '********************************************************************************************
            x1(i) = New XDate(yyyy, MM, DD, hh, min, ms).XLDate

            i = i + 1
        End While
        sqlConnection1.Close()
        dstop = x1(0)
        dstart = x1(i - 1)

        'Get Data older then 5 days
        sqlConnection1.Open()
        cmd.CommandText = "select top 2000 JDCOUNT, TimeDate, year(timedate), month(timedate), day(timedate), datepart(hour, timedate),datepart(minute, timedate),datepart(second, timedate) from f0008 where jddays = '5' and JDJBST = '" & JDJBST & "'  and jDsotp = '" & JDSOTP & "' and JDTECH = '" & JDTECH & "' order by TimeDate DESC"
        cmd.Connection = sqlConnection1

        reader = cmd.ExecuteReader()
        i = 0
        While reader.Read
            If i = 0 Then dTo = reader(1).ToString
            al1.Item(i) = Int(reader(0).ToString)
            dFrom = reader(1).ToString
            i = i + 1
        End While
        sqlConnection1.Close()


        Dim zgc As ZedGraphControl
        zgc = StatisticTech.zg2

        zgc.Refresh()

        zgc.ResetText()

        Dim myPane As GraphPane = zgc.GraphPane

        ' Set the titles and axis labels
        myPane.Title.Text = Title
        myPane.XAxis.Title.Text = xAxis
        myPane.XAxis.Scale.Min = dstart
        myPane.XAxis.Scale.Max = dstop
        myPane.YAxis.Scale.Min = 0
        myPane.YAxis.Title.Text = yAxis

        myPane.Y2Axis.Scale.Min = 0
        myPane.Y2Axis.IsVisible = True



        ' Make up some data points from the Sine function
        Dim list = New PointPairList()
        Dim list1 = New PointPairList()
        Dim x As Double, y As Double, y1 As Double
        For x = 0 To max - 1
            y = al.Item(x)
            y1 = al1.Item(x)
            'list.Add(1 - max + x, y)
            'list1.Add(1 - max + x, y + y1)
            list.Add(x1(x), y)
            list1.Add(x1(x), y1 + y)

        Next x

        ' Generate a blue curve with circle symbols, and "My Curve 2" in the legend
        myPane.CurveList.Clear()

        Dim myCurve As LineItem = myPane.AddCurve("New", list, Color.Blue, 9)
        Dim myCurve1 As LineItem = myPane.AddCurve("6 days old or more", list1, Color.Red, 9)

        ' Fill the area under the curve with a white-red gradient at 45 degrees

        myCurve.Line.IsSmooth = True
        myCurve1.Line.IsSmooth = True
        myCurve.Line.Fill = New Fill(Color.White, Color.Blue, 45.0F)
        myCurve1.Line.Fill = New Fill(Color.White, Color.Red, 45.0F)
        'myCurve.Symbol.Size = 0.0F
        'myCurve1.Symbol.Size = 0.0F
        myCurve.Symbol.IsVisible = False
        myCurve1.Symbol.IsVisible = False


        myPane.XAxis.Type = AxisType.Date

        ' Make the symbols opaque by filling them with white
        myCurve.Symbol.Fill = New Fill(Color.White)

        ' Fill the axis background with a color gradient
        myPane.Chart.Fill = New Fill(Color.White, Color.LightGoldenrodYellow, 45.0F)

        ' Fill the pane background with a color gradient
        myPane.Fill = New Fill(Color.White, Color.FromArgb(220, 220, 255), 45.0F)

        ' Calculate the Axis Scale Ranges
        zgc.AxisChange()


        'SetSize()
        zgc.Visible = True
        zgc.Refresh()

        StatisticsTech = True
    End Function

    Function ChageStatusInventory(ByVal SHSTNB As String, ByVal SHSTAT As String) As Boolean

        'Connection to M3
        Dim ConCM As OleDb.OleDbConnection
        Dim ReaderM3 As OleDb.OleDbDataReader
        Dim SQLString As String

        ChageStatusInventory = False

        ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
        ConCM.Open()

        'Check Lagerort und Item
        SQLString = "UPDATE MITTKH SET SHSTAT = '" & SHSTAT & "' WHERE SHCONO = 1 and SHSTNB =  " & SHSTNB & ""
        Dim CMDM As New OleDb.OleDbCommand(SQLString, ConCM)
        ReaderM3 = CMDM.ExecuteReader()
        ReaderM3.Read()
        ConCM.Close()

    End Function

    Function SetInterventionCode(ByVal ID As String, ByVal Storungscode As String) As Boolean

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim s As String = ""
        Dim q As String = ControlChars.Quote

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = "update AUF set Störungscode = '" & Storungscode & "' where ID =" & ID

        SetInterventionCode = True

        cmd.ExecuteNonQuery()
        sqlConnection1.Close()

    End Function

    Function AddSM(ByVal ID_TUER As String, ByVal Price As String, ByVal Stock As String) As Boolean

        Dim ID_ETEIL As String
        Dim sVar As Object = ""

        If Val(Price) > 50.0 And Val(Price) <= 249.99 Then
            ID_ETEIL = SQL_NNumber("ID_ETEIL")
            sVar = Split(ID_ETEIL & ";" & ID_TUER & ";ETEIL;103-999150;;" & Stock & ";;1;ST;1;10;", ";")
            SQL_Insert(sVar, "ETEIL")

        End If

        If Val(Price) > 250.0 And Val(Price) <= 999.99 Then
            ID_ETEIL = SQL_NNumber("ID_ETEIL")
            sVar = Split(ID_ETEIL & ";" & ID_TUER & ";ETEIL;103-999151;;" & Stock & ";;1;ST;1;10;", ";")
            SQL_Insert(sVar, "ETEIL")

        End If

        If Val(Price) > 1000 Then
            ID_ETEIL = SQL_NNumber("ID_ETEIL")
            sVar = Split(ID_ETEIL & ";" & ID_TUER & ";ETEIL;103-999152;;" & Stock & ";;1;ST;1;10;", ";")
            SQL_Insert(sVar, "ETEIL")

        End If

        AddSM = True
    End Function

    Function GetActivity(ByVal sDate As Date) As Boolean

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim reader1 As SqlClient.SqlDataReader = Nothing
        Dim oDate As String = Format(sDate, "yyyy-MM-dd")

        oDate = oDate & " 00:00:00.000"

        '****************************************************************
        'Get Data SV
        '****************************************************************
        sqlConnection1.Open()
        cmd.CommandText = "select count(*) as Anzahl from AUF where Auftragsart='SV' and Direction = 'R' " & _
                          "AND  TIMEDATE >= '" & oDate & "' " & _
                          "group by Month(TimeDate),   day(TimeDate),Year(TimeDate) " & _
                          "order by   Month(TimeDate),  day(TimeDate),Year(TimeDate)"
        cmd.Connection = sqlConnection1
        reader = cmd.ExecuteReader()

        If reader.Read Then
            Form1.SV.Text = reader(0).ToString
        Else
            Form1.SV.Text = "0"
        End If

        reader.Close()

        '****************************************************************
        'Get Data WM
        '****************************************************************
        cmd.CommandText = "select count(*) as Anzahl from AUF where Auftragsart='WM' and Direction = 'R' " & _
                          "AND  TIMEDATE >= '" & oDate & "' " & _
                          "group by Month(TimeDate),   day(TimeDate),Year(TimeDate) " & _
                          "order by   Month(TimeDate),  day(TimeDate),Year(TimeDate)"
        cmd.Connection = sqlConnection1
        reader = cmd.ExecuteReader()

        If reader.Read Then
            Form1.WM.Text = reader(0).ToString
        Else
            Form1.WM.Text = "0"
        End If

        reader.Close()

        '****************************************************************
        'Get Data NO
        '****************************************************************
        cmd.CommandText = "select count(*) as Anzahl from AUF where Auftragsart='NO' and Direction = 'R' " & _
                          "AND  TIMEDATE >= '" & oDate & "' " & _
                          "group by Month(TimeDate),   day(TimeDate),Year(TimeDate) " & _
                          "order by   Month(TimeDate),  day(TimeDate),Year(TimeDate)"
        cmd.Connection = sqlConnection1
        reader = cmd.ExecuteReader()

        If reader.Read Then
            Form1.NO.Text = reader(0).ToString
        Else
            Form1.NO.Text = "0"
        End If

        reader.Close()

        sqlConnection1.Close()

        GetActivity = True
    End Function

    Function GetGaugeValue(ByVal JLSOTP As String, ByVal JLJBST As String) As String

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing
        Dim i0 As String = "0"
        Dim i5 As String = "0"

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = "SELECT TOP 2 JLCOUNT FROM F0009 WHERE jlsotp = '" & JLSOTP & "'  and jljbst = " & JLJBST & " order by timedate desc"

        reader = cmd.ExecuteReader()

        If reader.Read Then
            i0 = reader(0).ToString
        Else
            i0 = "0"
        End If

        If reader.Read Then
            i5 = reader(0).ToString
        Else
            i5 = "0"
        End If

        sqlConnection1.Close()

        GetGaugeValue = Val(i0) + Val(i5)

    End Function

    Function GetTurnoverValue(ByVal JLYEAR As String, ByVal JLMOYR As String, ByRef JLPARN As String, ByRef JLWORN As String, ByRef JLTRAN As String, ByRef JLCOUNT As String, ByRef GTOTAL As String) As String

        'Give back the next number of the lable sName. 
        'If the lable does not exist it will create it.

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = "select  cast(sum(jlparn)/1000 as int), cast(sum(jlworn)/1000 as int), cast(sum(jltran)/1000 as int), cast(sum(jlcount)/1000 as int), cast(sum(gtotal)/1000 as int) from v1004 where jlyear = " & JLYEAR & " and jlmoyr = " & JLMOYR & ""

        reader = cmd.ExecuteReader()

        If reader.Read Then
            JLPARN = reader(0).ToString
            JLWORN = reader(1).ToString
            JLTRAN = reader(2).ToString
            JLCOUNT = reader(3).ToString
            GTOTAL = reader(4).ToString
        Else
            JLPARN = "0"
            JLWORN = "0"
            JLTRAN = "0"
            JLCOUNT = "0"
            GTOTAL = "0"
        End If


        sqlConnection1.Close()

        GetTurnoverValue = True

    End Function

    Public Function FormPos(ByRef Position As Point) As Point
        Dim intX As Integer = Screen.PrimaryScreen.Bounds.Width
        Dim intY As Integer = Screen.PrimaryScreen.Bounds.Height

        If Position.X > 0 And Position.X < intX - 30 Then  Else Position.X = 20
        If Position.Y > 0 And Position.Y < intY - 30 Then  Else Position.Y = 20

    End Function


End Module
